from flask import Flask, jsonify, request
from flask_cors import CORS
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import or_, func
from sqlalchemy import text
from collections import defaultdict
from werkzeug.security import generate_password_hash, check_password_hash
import random
import jieba
import jieba.analyse
from datetime import datetime, timedelta
import psutil
import subprocess
import threading
import logging
logging.basicConfig(level=logging.INFO)

app = Flask(__name__)
CORS(app)

# 配置数据库连接
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/books'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True  # 可选，用于调试SQL语句

db = SQLAlchemy(app)

global crawler_status
# 爬虫状态全局变量
crawler_status = {
    "is_running": False,
    "last_start_time": None,
    "pid": None
}

# 定义数据模型
class Book(db.Model):
    __tablename__ = 'books'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='自增主键')
    title = db.Column(db.String(255), nullable=False, comment='书名')
    author = db.Column(db.String(100), nullable=True, comment='作者')
    price = db.Column(db.Numeric(10, 2), nullable=True, comment='价格')
    image_url = db.Column(db.String(512), nullable=True, comment='封面图片URL')
    rating = db.Column(db.Numeric(3, 1), nullable=True, comment='评分(0-10分)')
    publisher = db.Column(db.String(100), nullable=True, comment='出版社')
    publish_date = db.Column(db.Date, nullable=True, comment='出版日期')
    review_count = db.Column(db.Integer, default=0, comment='评论数量')
    category_level1 = db.Column(db.String(50), nullable=True, comment='一级分类')
    category_level2 = db.Column(db.String(50), nullable=True, comment='二级分类')
    category_level3 = db.Column(db.String(50), nullable=True, comment='三级分类')
    created_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp(), comment='创建时间')
    updated_at = db.Column(
        db.TIMESTAMP,
        server_default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp(),
        comment='更新时间'
    )


# 定义 User 数据模型
class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='自增主键')
    username = db.Column(db.String(50), unique=True, nullable=False, comment='用户名')
    password = db.Column(db.String(100), nullable=False, comment='密码')
    type = db.Column(db.Enum('管理员', '分析师', '审计员'), nullable=False, comment='用户类型')


@app.route('/')
def hello_world():
    return 'Hello, Flask!'


@app.route('/api/books', methods=['GET'])
def get_books():
    try:
        # 获取查询参数
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 12, type=int)
        search = request.args.get('search', '')
        sort_field = request.args.get('sort', 'id')
        sort_order = request.args.get('order', 'asc')

        # 构建基础查询
        query = Book.query

        # 搜索功能
        if search:
            query = query.filter(or_(
                Book.title.ilike(f'%{search}%'),
                Book.author.ilike(f'%{search}%'),
                Book.publisher.ilike(f'%{search}%')
            ))

        # 排序功能
        if sort_field in ['id', 'title', 'author', 'price', 'rating', 'publish_date']:
            sort_column = getattr(Book, sort_field)
            if sort_order == 'desc':
                sort_column = sort_column.desc()
            query = query.order_by(sort_column)

        # 分页
        pagination = query.paginate(page=page, per_page=per_page, error_out=False)
        books = pagination.items

        # 序列化结果
        result = [{
            'id': book.id,
            'title': book.title,
            'author': book.author,
            'price': float(book.price) if book.price else None,
            'image_url': book.image_url,
            'rating': float(book.rating) if book.rating else None,
            'publisher': book.publisher,
            'publish_date': book.publish_date.isoformat() if book.publish_date else None,
            'review_count': book.review_count,
            'categories': {
                'level1': book.category_level1,
                'level2': book.category_level2,
                'level3': book.category_level3
            }
        } for book in books]

        return jsonify({
            "status": "success",
            "data": result,
            "pagination": {
                "total": pagination.total,
                "pages": pagination.pages,
                "current_page": pagination.page,
                "per_page": pagination.per_page,
                "has_next": pagination.has_next,
                "has_prev": pagination.has_prev
            }
        })

    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


@app.route('/api/books/<int:book_id>', methods=['GET'])
def get_book_detail(book_id):
    try:
        book = Book.query.get_or_404(book_id)

        return jsonify({
            "status": "success",
            "data": {
                'id': book.id,
                'title': book.title,
                'author': book.author,
                'price': float(book.price) if book.price else None,
                'image_url': book.image_url,
                'rating': float(book.rating) if book.rating else None,
                'publisher': book.publisher,
                'publish_date': book.publish_date.isoformat() if book.publish_date else None,
                'review_count': book.review_count,
                'categories': {
                    'level1': book.category_level1,
                    'level2': book.category_level2,
                    'level3': book.category_level3
                },
                'created_at': book.created_at.isoformat() if book.created_at else None,
                'updated_at': book.updated_at.isoformat() if book.updated_at else None
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


@app.route('/api/books/categories', methods=['GET'])
def get_categories():
    try:
        # 获取所有一级分类
        level1 = db.session.query(
            Book.category_level1.distinct().label('category')
        ).filter(Book.category_level1.isnot(None)).all()

        # 获取所有二级分类
        level2 = db.session.query(
            Book.category_level2.distinct().label('category')
        ).filter(Book.category_level2.isnot(None)).all()

        # 获取所有三级分类
        level3 = db.session.query(
            Book.category_level3.distinct().label('category')
        ).filter(Book.category_level3.isnot(None)).all()

        return jsonify({
            "status": "success",
            "data": {
                "level1": [c.category for c in level1],
                "level2": [c.category for c in level2],
                "level3": [c.category for c in level3]
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


@app.route('/api/books/stats', methods=['GET'])
def get_book_stats():
    try:
        # 图书总数
        total_books = db.session.query(func.count(Book.id)).scalar()

        # 平均价格
        avg_price = db.session.query(func.avg(Book.price)).scalar()

        # 平均评分
        avg_rating = db.session.query(func.avg(Book.rating)).scalar()

        # 出版社统计
        publishers = db.session.query(
            Book.publisher,
            func.count(Book.id).label('count')
        ).group_by(Book.publisher).order_by(func.count(Book.id).desc()).limit(10).all()

        return jsonify({
            "status": "success",
            "data": {
                "total_books": total_books,
                "avg_price": float(avg_price) if avg_price else None,
                "avg_rating": float(avg_rating) if avg_price else None,
                "top_publishers": [{
                    "name": p[0],
                    "count": p[1]
                } for p in publishers]
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


@app.route('/api/books/category-tree', methods=['GET'])
def get_category_tree():
    try:
        # 获取完整的分类层次结构
        categories = db.session.query(
            Book.category_level1,
            Book.category_level2,
            Book.category_level3
        ).filter(
            Book.category_level1.isnot(None)
        ).distinct().all()

        # 构建树形结构
        tree = defaultdict(lambda: defaultdict(list))
        for l1, l2, l3 in categories:
            if l1 and l2 and l3:
                tree[l1][l2].append(l3)
            elif l1 and l2:
                tree[l1][l2] = []
            elif l1:
                tree[l1] = defaultdict(list)

        return jsonify({
            "status": "success",
            "data": tree
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 用户注册
@app.route('/api/register', methods=['POST'])
def register():
    try:
        data = request.json
        username = data.get('username')
        password = data.get('password')
        user_type = data.get('type')

        # 验证用户类型
        if user_type not in ['管理员', '分析师', '审计员']:
            return jsonify({
                "status": "error",
                "message": "无效的用户类型，请选择 '管理员'、'分析师' 或 '审计员'"
            }), 400

        if not username or not password:
            return jsonify({
                "status": "error",
                "message": "用户名和密码不能为空"
            }), 400

        # 检查用户名是否已存在
        existing_user = User.query.filter_by(username=username).first()
        if existing_user:
            return jsonify({
                "status": "error",
                "message": "用户名已存在"
            }), 400

        # 密码加密
        hashed_password = generate_password_hash(password)

        # 创建新用户
        new_user = User(username=username, password=hashed_password, type=user_type)
        db.session.add(new_user)
        db.session.commit()

        return jsonify({
            "status": "success",
            "message": "注册成功",
            "user": {
                "id": new_user.id,
                "username": new_user.username,
                "type": new_user.type
            }
        })

    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 用户登录
@app.route('/api/login', methods=['POST'])
def login():
    try:
        data = request.json
        username = data.get('username')
        password = data.get('password')

        if not username or not password:
            return jsonify({
                "status": "error",
                "message": "用户名和密码不能为空"
            }), 400

        # 验证用户
        user = User.query.filter_by(username=username).first()

        if user and check_password_hash(user.password, password):
            # 返回用户类型
            return jsonify({
                "status": "success",
                "message": "登录成功",
                "user": {
                    "id": user.id,
                    "username": user.username,
                    "type": user.type
                }
            })
        else:
            return jsonify({
                "status": "error",
                "message": "用户名或密码错误"
            }), 401

    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500



# 新增路由 - 获取子分类
@app.route('/api/books/subcategories', methods=['GET'])
def get_subcategories():
    try:
        level = request.args.get('level', '1')
        parent = request.args.get('parent', '')

        if level == '1':
            subcategories = db.session.query(
                Book.category_level1.distinct().label('category')
            ).filter(Book.category_level1.isnot(None)).all()
        elif level == '2' and parent:
            subcategories = db.session.query(
                Book.category_level2.distinct().label('category')
            ).filter(
                Book.category_level1 == parent,
                Book.category_level2.isnot(None)
            ).all()
        elif level == '3' and parent:
            subcategories = db.session.query(
                Book.category_level3.distinct().label('category')
            ).filter(
                Book.category_level2 == parent,
                Book.category_level3.isnot(None)
            ).all()
        else:
            subcategories = []

        return jsonify({
            "status": "success",
            "data": [c.category for c in subcategories]
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 分类桑基图数据
@app.route('/api/books/category-sankey', methods=['GET'])
def get_category_sankey():
    try:
        # 获取分类流转数据
        flow_data = db.session.query(
            Book.category_level1,
            Book.category_level2,
            Book.category_level3,
            func.count(Book.id).label('count')
        ).group_by(
            Book.category_level1,
            Book.category_level2,
            Book.category_level3
        ).all()

        # 构建桑基图所需的数据结构
        nodes = set()
        links = []
        added_links = set()  # 用于防止重复链接
        graph = {}  # 用于检测循环

        # 添加所有节点并初始化图结构
        for l1, l2, l3, count in flow_data:
            if l1:
                nodes.add(l1)
                if l1 not in graph:
                    graph[l1] = []
            if l2:
                nodes.add(l2)
                if l2 not in graph:
                    graph[l2] = []
            if l3:
                nodes.add(l3)
                if l3 not in graph:
                    graph[l3] = []

        # 构建连接关系并检测循环
        for l1, l2, l3, count in flow_data:
            if l1 and l2:
                link_key = f"{l1}->{l2}"
                if link_key not in added_links:
                    # 检查是否会形成循环
                    if not will_create_cycle(graph, l1, l2):
                        links.append({
                            'source': l1,
                            'target': l2,
                            'value': count
                        })
                        added_links.add(link_key)
                        graph[l1].append(l2)  # 添加到图中

            if l2 and l3:
                link_key = f"{l2}->{l3}"
                if link_key not in added_links:
                    # 检查是否会形成循环
                    if not will_create_cycle(graph, l2, l3):
                        links.append({
                            'source': l2,
                            'target': l3,
                            'value': count
                        })
                        added_links.add(link_key)
                        graph[l2].append(l3)  # 添加到图中

        return jsonify({
            "status": "success",
            "data": {
                "nodes": [{"name": n} for n in nodes],
                "links": links
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


def will_create_cycle(graph, source, target):
    """
    检查添加source->target边是否会形成循环
    使用深度优先搜索(DFS)检测循环
    """
    visited = set()
    stack = [target]

    while stack:
        node = stack.pop()
        if node == source:
            return True  # 发现循环

        if node not in visited:
            visited.add(node)
            for neighbor in graph.get(node, []):
                stack.append(neighbor)

    return False


# 新增路由 - 分类关键词词云
@app.route('/api/books/category-keywords', methods=['GET'])
def get_category_keywords():
    try:
        level = request.args.get('level', '1')
        category = request.args.get('category', '')

        # 根据分类级别构建查询
        query = Book.query
        if level == '1' and category:
            query = query.filter(Book.category_level1 == category)
        elif level == '2' and category:
            query = query.filter(Book.category_level2 == category)
        elif level == '3' and category:
            query = query.filter(Book.category_level3 == category)

        # 获取所有标题文本
        titles = [b.title for b in query.all() if b.title]
        text = ' '.join(titles)

        # 使用jieba提取关键词
        jieba.analyse.set_stop_words('stopwords.txt')  # 需要准备停用词文件
        keywords = jieba.analyse.extract_tags(
            text,
            topK=50,
            withWeight=True,
            allowPOS=('n', 'vn', 'v', 'eng')
        )

        return jsonify({
            "status": "success",
            "data": [{"name": k[0], "value": k[1]} for k in keywords]
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 分类健康度评分
@app.route('/api/books/category-health', methods=['GET'])
def get_category_health():
    try:
        level = request.args.get('level', '1')
        category = request.args.get('category', '')

        # 构建基础查询
        query = Book.query
        if level == '1' and category:
            query = query.filter(Book.category_level1 == category)
        elif level == '2' and category:
            query = query.filter(Book.category_level2 == category)
        elif level == '3' and category:
            query = query.filter(Book.category_level3 == category)

        # 计算各项指标
        now = datetime.now()
        thirty_days_ago = now - timedelta(days=30)

        # 商品数量
        product_count = query.count()

        # 更新频率（最近30天更新的商品比例）
        update_frequency = query.filter(
            Book.updated_at >= thirty_days_ago
        ).count() / product_count if product_count > 0 else 0

        # 价格竞争力（低于平均价格的商品比例）
        avg_price = db.session.query(
            func.avg(Book.price)
        ).scalar() or 0

        price_competitiveness = query.filter(
            Book.price <= avg_price
        ).count() / product_count if product_count > 0 else 0

        # 综合评分（可根据业务需求调整权重）
        score = (
                        0.4 * min(product_count / 100, 1) +  # 商品数量权重40%，最大100为满分
                        0.3 * update_frequency +  # 更新频率权重30%
                        0.3 * price_competitiveness  # 价格竞争力权重30%
                ) * 10  # 转换为10分制

        return jsonify({
            "status": "success",
            "data": {
                "product_count": product_count,
                "update_frequency": update_frequency,
                "price_competitiveness": price_competitiveness,
                "health_score": round(score, 1),
                "metrics": [
                    {"name": "商品数量", "value": product_count},
                    {"name": "更新频率", "value": f"{update_frequency:.1%}"},
                    {"name": "价格竞争力", "value": f"{price_competitiveness:.1%}"}
                ]
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 价格分布环形图数据
@app.route('/api/dashboard/price-distribution', methods=['GET'])
def get_price_distribution():
    try:
        # 定义价格区间
        ranges = [
            (0, 50),
            (50, 100),
            (100, 200),
            (200, 500),
            (500, None)
        ]

        results = []
        for min_price, max_price in ranges:
            query = db.session.query(func.count(Book.id))
            if min_price is not None:
                query = query.filter(Book.price >= min_price)
            if max_price is not None:
                query = query.filter(Book.price < max_price)
            else:
                query = query.filter(Book.price >= min_price)

            count = query.scalar()
            if max_price:
                label = f"{min_price}-{max_price}元"
            else:
                label = f"{min_price}元以上"

            results.append({
                "name": label,
                "value": count
            })

        return jsonify({
            "status": "success",
            "data": results
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 分类商品数量TOP10
@app.route('/api/dashboard/category-top10', methods=['GET'])
def get_category_top10():
    try:
        # 获取一级分类的商品数量TOP10
        top_categories = db.session.query(
            Book.category_level1,
            func.count(Book.id).label('count')
        ).filter(
            Book.category_level1.isnot(None)
        ).group_by(
            Book.category_level1
        ).order_by(
            func.count(Book.id).desc()
        ).limit(10).all()

        # 转换为前端需要的格式
        categories = [item[0] for item in top_categories]
        counts = [item[1] for item in top_categories]

        return jsonify({
            "status": "success",
            "data": {
                "categories": categories,
                "counts": counts
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


# 新增路由 - 价格波动折线图数据
@app.route('/api/dashboard/price-trend', methods=['GET'])
def get_price_trend():
    try:
        book_id = request.args.get('book_id', type=int)
        days = request.args.get('days', 7, type=int)

        if not book_id:
            return jsonify({
                "status": "error",
                "message": "book_id参数必填"
            }), 400

        # 模拟数据 - 实际项目中应该有价格历史表
        dates = [(datetime.now() - timedelta(days=i)).date() for i in range(days, -1, -1)]
        prices = [round(50 + 10 * (i / days) + random.uniform(-5, 5), 2) for i in range(days + 1)]

        return jsonify({
            "status": "success",
            "data": {
                "dates": [date.isoformat() for date in dates],
                "prices": prices,
                "book_id": book_id,
                "book_title": Book.query.get(book_id).title if Book.query.get(book_id) else "未知"
            }
        })
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500


def monitor_crawler(process):
    """监控爬虫进程的线程函数"""
    process.wait()  # 等待进程结束
    crawler_status.update({
        "is_running": False,
        "pid": None
    })
    print("爬虫进程已结束")


def check_crawler_process():
    """检查爬虫进程是否在运行"""
    if crawler_status["pid"]:
        try:
            process = psutil.Process(crawler_status["pid"])
            return process.is_running()
        except:
            return False
    return False


@app.route('/api/crawler/start', methods=['POST'])
def start_crawler():
    """启动爬虫"""
    if crawler_status["is_running"] and check_crawler_process():
        return jsonify({"success": False, "message": "爬虫正在运行中"})

    try:
        # 使用子进程启动爬虫（异步）
        process = subprocess.Popen(['python', 'spider.py'])

        # 记录状态
        crawler_status.update({
            "is_running": True,
            "last_start_time": datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            "pid": process.pid
        })

        # 启动监控线程
        threading.Thread(target=monitor_crawler, args=(process,), daemon=True).start()

        return jsonify({"success": True, "message": "爬虫启动成功"})
    except Exception as e:
        return jsonify({"success": False, "message": str(e)})


@app.route('/api/crawler/stop', methods=['POST'])
def stop_crawler():
    logging.info(f"尝试停止PID: {crawler_status['pid']}")


    # 1. 更严格的状态检查
    if not crawler_status["pid"]:
        return jsonify({"success": False, "message": "没有运行的爬虫进程"})

    # 可以只使用 psutil 来终止进程
    try:
        process = psutil.Process(crawler_status["pid"])
        process.terminate()  # 更优雅的终止方式
        process.wait(timeout=5)  # 等待进程结束
        crawler_status.update({"is_running": False, "pid": None})
        return jsonify({"success": True})
    except psutil.NoSuchProcess:
        crawler_status.update({"is_running": False, "pid": None})
        return jsonify({"success": False, "message": "进程不存在"})

@app.route('/api/crawler/status', methods=['GET'])
def get_crawler_status():
    """获取爬虫状态"""
    return jsonify({
        "success": True,
        "data": {  # 确保有data包装层
            "isRunning": crawler_status["is_running"],
            "pid": crawler_status["pid"],
            "lastStartTime": crawler_status["last_start_time"]
        }
    })

def check_db_health():
    try:
        db.session.execute(text("SELECT 1"))  # 正确写法
        Book.query.limit(1).all()  # 再测试一次 ORM 查询
        return 'normal'
    except Exception as e:
        print(f"数据库健康检查失败: {str(e)}")
        return 'error'

# 仪表盘数据API（改用SQLAlchemy查询）
@app.route('/api/dashboard/overview', methods=['GET'])
def get_dashboard_overview():
    try:
        # 1. 获取今日爬取数量
        today = datetime.now().strftime('%Y-%m-%d')
        today_count = db.session.query(db.func.count(Book.id)) \
            .filter(db.func.date(Book.created_at) == today) \
            .scalar()

        # 2. 获取总数量
        target_count = 50000  # 假设目标
        completed_count = db.session.query(db.func.count(Book.id)).scalar()
        progress = min(round(completed_count / target_count * 100, 2), 100)

        # 3. 数据库状态检查
        try:
            db.session.execute(text("SELECT 1"))
            db_status = check_db_health()
        except Exception as e:
            db_status = check_db_health()
            print(f"数据库检查失败: {str(e)}")

        # 4. 获取7天趋势（使用SQLAlchemy的func方法）
        seven_days_ago = datetime.now() - timedelta(days=6)
        trend_data = db.session.query(
            db.func.date(Book.created_at).label('date'),
            db.func.count(Book.id).label('count')
        ).filter(Book.created_at >= seven_days_ago) \
         .group_by(db.func.date(Book.created_at)) \
         .order_by('date') \
         .all()

        return jsonify({
            'success': True,
            'data': {
                'progress': progress,
                'todayCount': today_count or 0,
                'targetCount': target_count,
                'completedCount': completed_count or 0,
                'dbStatus': db_status,
                'trendData': [{
                    'date': item.date.strftime('%Y-%m-%d'),
                    'count': item.count
                } for item in trend_data]
            }
        })
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

if __name__ == '__main__':
    with app.app_context():
        db.create_all()  # 确保表存在
    app.run(host='0.0.0.0', port=5000, debug=True)